﻿using EFCore.Context;
using EFCore.Model;
using Microsoft.EntityFrameworkCore;

namespace EFCore.Repositories
{
    public class SeckillRepository : ISeckillRepository
    {
        public SeckillContext SeckillContext { get; set; }
        public SqlSugarContext sugarContext { get; set; }
        public SeckillRepository(SeckillContext seckillContext, SqlSugarContext sqlSugar)
        {
            SeckillContext = seckillContext;
            sugarContext = sqlSugar;
        }

        /// <summary>
        /// 查询秒杀商品接口
        /// </summary>
        /// <returns></returns>
        public IEnumerable<Seckill> GetSeckills()
        {
            //var result = SeckillContext.Seckills.ToList();
            //return result;
            // 1、模糊查询商品列表。 1、全文索引
            #region 1、根据条件模糊查询商品.通过商品名称
            {
                /*// 匹配结束(不会走索引) like
                var Seckill2 = SeckillContext.Seckills.Where(p => p.SeckillName.EndsWith("P")).ToList();

                // 匹配开始(会走索引) like
                var Seckill1 = SeckillContext.Seckills.Where(p => p.SeckillName.StartsWith("P")).ToList();

                // 匹配开始(不会走索引) like
                var Seckill3 = SeckillContext.Seckills.Where(p => p.SeckillName.Contains("P")).ToList();*/
            }
            #endregion

            // 2、客户端要求查询商品。只返回商品价格
            #region 2、指定列查询。
            {
                // 1、所有列查询
                //foreach (var seckill in SeckillContext.Seckills)
                //{
                //    Console.WriteLine("Seckill: " + seckill.SeckillPrice);
                //}

                //// 2、指定列查询
                //foreach (var SeckillPrice in SeckillContext.Seckills.Select(b => new { b.SeckillPrice, b.SeckillName }).ToList())
                //{
                //    Console.WriteLine("Seckill: " + SeckillPrice);
                //}
            }
            #endregion

            // 3、批量查询商品数据。
            #region 3、限制结果集  Take limit
            {
                // 1、查询结果集
                //var seckills1 = SeckillContext.Seckills
                //.Where(p => p.SeckillName.StartsWith("P"))
                //.ToList();

                //// 2、限制结果集
                //var seckills2 = SeckillContext.Seckills
                //.Where(p => p.SeckillName.StartsWith("P"))
                //.Skip(5) // 数据的偏移量
                //.Take(5) // 限制结果集数量。
                //.ToList();
            }
            #endregion

            #region 4、缓冲和流式处理
            {
                // 1、流式处理(遍历查询每次查询数据库）

                //var seckills = SeckillContext.Seckills.Where(p => p.SeckillName.StartsWith("P"));
                //foreach (var seckill in seckills)
                //{
                //    Console.WriteLine(seckill.SeckillName);
                //}

                //// 2、缓冲处理(分页查询。非常.ToList() ToArray())
                //var seckills1 = SeckillContext.Seckills.Where(p => p.SeckillName.StartsWith("P")).ToList();
                //var seckills2 = SeckillContext.Seckills.Where(p => p.SeckillName.StartsWith("P")).ToArray();
                // 3、流式处理(内存资源)
                //var seckills3 = SeckillContext.Seckills
                //.Where(p => p.SeckillName.StartsWith("P"))
                //.AsEnumerable();

                //foreach (var seckill in seckills3) 
                //{
                //    Console.WriteLine(seckill.SeckillName);
                //}
            }
            #endregion

            #region 5、尽量使用非跟踪查询
            {
                //// 1、跟踪查询
                //var seckills1 = SeckillContext.Seckills.Where(p => p.SeckillName.StartsWith("P")).AsTracking().ToList();
                //seckills1[0].SeckillName = "5555";
                //SeckillContext.Seckills.Update(seckills1[0]);
                //SeckillContext.SaveChanges();

                ////var data = sugarContext.sqlSugarClient.Queryable<Seckill>().First() ;
                ////data.SeckillName = "pxxxxxxxxxxx";
                ////sugarContext.sqlSugarClient.Updateable<Seckill>(data).ExecuteCommand();

                //// 2、非跟踪查询
                //var seckills2 = SeckillContext.Seckills.Where(p => p.SeckillName.StartsWith("P")).AsNoTracking().ToList();
            }
            #endregion

            #region 6、使用异步
            {
                /*// 1、同步处理
                var sec = SeckillContext.Seckills.Where(b => b.SeckillName.StartsWith("P")).ToList();

                // 2、异步处理。时间来获取先后顺序
                var blogs = await SeckillContext.Seckills.Where(b => b.SeckillName.StartsWith("P")).ToListAsync();*/
            }
            #endregion

            // 4、关联查询。查询商品--商品浏览记录。懒加载----> 
            #region 7、关联查询尽量预加载  查询商品--商品浏览记录
            {
                //// 1、关联延迟加载。会出现 1+N问题。1+N 一次商品。N次商品记录查询。
                //var seckills = SeckillContext.Seckills.ToList();
                //foreach (var seckill in seckills)
                //{
                //    //foreach (var seckillRecord in seckill.seckillRecords)
                //    //{
                //    //    Console.WriteLine($"seckillRecord {seckillRecord.RecordTotalprice}");
                //    //}
                //}

                //// 2、关联预先加载。一次性所有加载。
                //var seckills1 = SeckillContext.Seckills.Where(x => x.Id == 1).Include(seckill => seckill.seckillRecords).ToList();
                //foreach (var seckill in seckills1)
                //{
                //    //foreach (var seckillRecord in seckill.seckillRecords)
                //    //{
                //    //    Console.WriteLine($"seckillRecord {seckillRecord.RecordTotalprice}");
                //    //}
                //}
                ////return seckills;
            }
            #endregion

            #region 8、关联查询(避免笛卡尔积) // 1、查询商品---->商品记录返回
            {
                //// 1、关联查询(预先加载)
                //var seckills1 = SeckillContext.Seckills
                //.Include(seckill => seckill.seckillRecords)
                //.ToList();

                //// 2、关联拆分查询(预先加载笛卡尔积的问题)
                //var seckills2 = SeckillContext.Seckills
                //.Include(seckills => seckills.seckillRecords)
                //.AsSplitQuery() // efcore 5.0 .AsSingleQuery()//分开查询。
                //.ToList();
            }
            #endregion

            // 5、查询大量的商品。100万商品数据。 1000万。不想限制。通过流式处理。

            #region 9、使用原始sql
            {
                // 1、sql查询
                /*var seckills = SeckillContext.Seckills.FromSqlRaw("select * from seckills").ToList();

                // 2、原始sql执行存储过程
                var blogs = SeckillContext.Seckills
                .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")
                .ToList();*/
            }
            #endregion

            #region 10、批处理(默认执行42个。默认只会执行42个)
            {
                //// 1、多个sql语句操作（默认用的是：跟踪）
                //var seckill1 = SeckillContext.Seckills.First(b => b.SeckillName == "iPhone"); // 单个数据查询
                //seckill1.SeckillName = "iPhone";
                //SeckillContext.SaveChanges();

                //SeckillContext.Seckills.Add(new Seckill { SeckillName = "phone3" });
                //SeckillContext.SaveChanges();

                //SeckillContext.Seckills.Add(new Seckill { SeckillName = "phone4" });
                //SeckillContext.SaveChanges();

                //// 2、批处理(跟踪更新)
                //var seckill2 = SeckillContext.Seckills.First(b => b.SeckillName == "iPhone");
                //seckill2.SeckillName = "iPhone 13";
                //SeckillContext.Add(new Seckill { SeckillName = "phone2" });
                //SeckillContext.Add(new Seckill { SeckillName = "phone3" });
                //// 41条批量语句
                //// 100000万条。直接sql
                //SeckillContext.SaveChanges();

            }
            #endregion

            #region 11、大容量更新
            {
                //// 1、默认批处理10万条。
                //foreach (var seckill in SeckillContext.Seckills.ToList())
                //{
                //    seckill.SeckillName = "xxx"; // 批量一个一个更新
                //}
                //SeckillContext.SaveChanges();

                //// 2、直接sql处理（减少客户端数据）
                //SeckillContext.Seckills.FromSqlRaw("UPDATE [Seckills] SET [SeckillName] = 'Tony'");
            }
            #endregion

            #region 12、DbContext连接池
            {
                /*services.AddDbContextPool<BloggingContext>(
                  options => options.UseSqlServer(connectionString));*/
            }
            #endregion

            // 高级性能主题 
            #region 13、查询缓存和参数化(预编译) 编译预先的sql。复用sql语句，提升性能目的
            {
                //// 1、固定值查询（生成2条sql）直接生成sql
                //var seckill1 = SeckillContext.Seckills.FirstOrDefault(p => p.SeckillName == "xxx");
                //var seckill2 = SeckillContext.Seckills.FirstOrDefault(p => p.SeckillName == "xxx");

                //// 2、参数化查询（生成一条sql）原理 ：sql参数缓存
                //var SeckillName = "xxx";
                //var seckill3 = SeckillContext.Seckills.FirstOrDefault(p => p.SeckillName == SeckillName);
                //SeckillName = "xxx";
                //var seckill4 = SeckillContext.Seckills.FirstOrDefault(p => p.SeckillName == SeckillName);
            }
            #endregion

            // 查询商品：商品名称，商品价格，商品ID
            #region 14、动态拼接查询
            {
                //IQueryable<Seckill> seckills = SeckillContext.Seckills;

                //if (seckill.SeckillName != null && seckill.SeckillName.Length > 0)
                //{
                //    seckills = seckills.Where(b => b.SeckillName == seckill.SeckillName);
                //}
                var seckill = SeckillContext.Seckills.FirstOrDefault(p => p.SeckillName == "xxx");
                IQueryable<Seckill> query = SeckillContext.Seckills;
                query = query.Where(s => s.Id == seckill.Id);
                query = query.Where(s => s.SeckillName == seckill.SeckillName);
                query = query.Where(s => s.SeckillUrl == seckill.SeckillUrl);
            }
            #endregion
            return null;
        }

        public void Update(Seckill Seckill)
        {
            SeckillContext.Seckills.Update(Seckill);
            SeckillContext.SaveChanges();
        }
        public bool SeckillExists(int id)
        {
            return SeckillContext.Seckills.Any(e => e.Id == id);
        }

        public IEnumerable<Seckill> GetSeckills(Seckill seckill)
        {
            IQueryable<Seckill> query = SeckillContext.Seckills;
            if (seckill.Id != 0)
            {
                query = query.Where(s => s.Id == seckill.Id);
            }
            if (seckill.SeckillType != 0)
            {
                query = query.Where(s => s.SeckillType == seckill.SeckillType);
            }
            if (seckill.SeckillName != null)
            {
                query = query.Where(s => s.SeckillName == seckill.SeckillName);
            }
            if (seckill.SeckillUrl != null)
            {
                query = query.Where(s => s.SeckillUrl == seckill.SeckillUrl);
            }
            if (seckill.SeckillPrice != 0)
            {
                query = query.Where(s => s.SeckillPrice == seckill.SeckillPrice);
            }
            if (seckill.SeckillStock != 0)
            {
                query = query.Where(s => s.SeckillStock == seckill.SeckillStock);
            }
            if (seckill.SeckillPercent != null)
            {
                query = query.Where(s => s.SeckillPercent == seckill.SeckillPercent);
            }
            if (seckill.TimeId != 0)
            {
                query = query.Where(s => s.TimeId == seckill.TimeId);
            }
            if (seckill.ProductId != 0)
            {
                query = query.Where(s => s.ProductId == seckill.ProductId);
            }
            if (seckill.SeckillLimit != 0)
            {
                query = query.Where(s => s.SeckillLimit == seckill.SeckillLimit);
            }
            if (seckill.SeckillDescription != null)
            {
                query = query.Where(s => s.SeckillDescription == seckill.SeckillDescription);
            }
            if (seckill.SeckillIstop != 0)
            {
                query = query.Where(s => s.SeckillIstop == seckill.SeckillIstop);
            }
            if (seckill.SeckillStatus != 0)
            {
                query = query.Where(s => s.SeckillStatus == seckill.SeckillStatus);
            }
            return query;
        }

        public Seckill GetSeckillByProductId(int ProductId)
        {
            List<Seckill> seckills = SeckillContext.Seckills.Where(s => s.ProductId == ProductId).ToList();

            return seckills[0];
        }
    }
}
